Stored Procedure Test

The book has now been published and the content of this chapter has likely changed substanstially.
Please see page 654 of xUnit Test Patterns for the latest information.
How can we verify logic independently when we have stored procedures?

We write Fully Automated Tests for each stored procedure.

Sketch Stored Procedure Test embedded from Stored Procedure Test.gif

Many applications that use a database to store the persistent state of the application also use stored procedures and triggers to improve performance and to do common processing on updates.

A Stored Procedure Test is a way to apply automated testing practices to this code that lives inside the database.

How It Works

We write unit tests for the stored procedures independent of the client application software. These tests may be layer-crossing tests or round trip tests depending on the nature of the store procedure(s) being tested.

When To Use It

We should write Stored Procedure Tests whenever we have non-trivial logic in stored procedures. This will help us verify that the stored procedures (our system under test (SUT) for the purposes of these tests) is working properly independently of the client application. This is particularly important when the stored procedures are likely to be used by more than one application and we cannot ensure they are tested adequately simply by exercising the application software (a form of Indirect Testing (see Obscure Test on page X).) Using Stored Procedure Tests also helps us to enumerate all the conditions under which the stored procedure could be called and what should happen in each circumstance. The very act of thinking about these circumstances is likely to improve the design (a common result of doing test-first development.)

Implementation Notes

There are two fundamentally different ways to implement Stored Procedure Tests. We can write the tests in the same programming language as the stored procedure and run them in the database or we can write them in our application programming language and access the stored procedure via a Remote Proxy[GOF]. We might even write tests both ways; the stored procedure developers might write unit tests in the database programming language and the application developers might prepare some acceptance tests in the application programming language to run as part of the application build.

Either way, we also need to decide how the test will set up the fixture (the "before" state of the database) and verify the expected outcome (the "after" state of the database as well as any expected actions such as cascading deletes.) The test may interact directly with the database to insert/verify the data (a form of Back Door Manipulation (page X)) or it could use another stored procedure (a form of round trip test.)

Variation: In-Database Stored Procedure Test

One of the advantages of the xUnit approach to automated testing is that the tests are written in the same language as the code we are testing. This makes it easier for the developers to learn how to automate the tests without learning a new programming language, debugger, etc. Taking this to its logical conclusion, it makes sense to test stored procedures using tests that are written in the stored procedure programming language. Naturally, we will need to run these tests inside the database. That may make it hard to run them as part of the Integration Build[SCM].

This way of implementing Stored Procedure Test is appropriate when we have more experience writing code in the stored procedure language and/or environment than in the application environment and it is not essential that all the tests can be run from a single place. A database or data services team writing stored procedures for use by other teams would be a good example of this. Another reason to use In-Database Stored Procedure Tests is if the stored procedures are stored in a different source code repository than the application logic. Using In-Database Stored Procedure Test allows us to store the tests in the same repository as the SUT (in this case, the stored procedures.)

In-Database Stored Procedure Tests may allow somewhat more thorough unit testing (and TDD) of the stored procedures because we may be able to get better access to implementation details of the stored procedure from our tests. Of course, this violation of encapsulation could result in Overspecified Software (see Fragile Test on page X). If the client code uses a data access layer, we will still have to write unit tests for that software in the application programming language to ensure we are handling errors correctly (e.g. failure to connect, etc..)

Some databases support several programming languages. In these cases we can choose to use the more test-friendly programming language for our tests while still writing the stored procedures in the more traditional stored procedure programming language. For example, Oracle databases support both PLSQL and Java so we could use JUnit tests to verify our PLSQL stored procedures. Likewise, SQL Server supports C# so we could use NUnit tests written in C# to verify the stored procedures written in Transact-SQL.

Variation: Remoted Stored Procedure Test

The purpose of Remoted Stored Procedure Tests is to allow us to write the tests in the same language as the unit tests for the client application logic. We must access the stored procedure(s) via a Remote Proxy [GOF] that hides the mechanics of interacting with the the stored procedure. The proxy can be structured either as a Service Facade[CJ2EEP] or a Command[GOF] (such as Java's JdbcOdbcCallableStatement.)

Remoted Stored Procedure Tests are in effect component tests since they treat the stored procedure as a black box component. Since a Remoted Stored Procedure Test does not run inside the database, we are more likely to write them as round trip tests (calling other stored procedures to set up the fixture, verify the outcome, etc.) unless we have an easy way to insert or verify data. Some members of the xUnit family have extensions specifically to make this easier (e.g. DbUnit for Java and NDbUnit for .Net languages.)

This solution is more appropriate if we want to keep all our tests in a single programming language. This makes it easier to run all the tests at every check in. This is particularly useful if the stored procedures are being written and/or modified by the team that is also developing the client code. We can also use Remoted Stored Procedure Tests when another team is providing the stored procedures and we are not confident in their ability to write defect-free code (probably because they are not writing In-Database Stored Procedure Tests for their code.) We can use the Remoted Stored Procedure Tests as a form of acceptance test for their code. See the sidebar Testing Stored Procs with JUnit (page X) for how this work out for us on one project.

One disadvantage of using Remoted Stored Procedure Tests is that they will likely cause the test suite to run slower because the tests require the database to be available and to be populated with data. The tests for the stored procedures can be put into a separate Subset Suite (see Named Test Suite on page X) so that they need not be run with all the in-memory tests. This can significantly speed up test execution thus avoiding Slow Tests (page X).

Remoted Stored Procedure Tests also come in handy when we have logic written in our programming language of choice that already has unit tests and we need to move the logic into the database. By using a Stored Procedure Test, we can avoid rewriting the tests in a different programming language and Test Automation Framework (page X) which can save time and money as well as avoiding any translation errors when recoding the logic; we can be sure the recoded logic really does produce the same results.

Motivating Example

Here is an example of a stored procedure written in PLSQL.

CREATE OR REPLACE PROCEDURE calc_secs_between (
   date1 IN DATE,
   date2 IN DATE,
   secs OUT NUMBER
)
IS
BEGIN
   secs := (date2 - date1) * 24 * 60 * 60;
END;
/

Example StoredProcedure embedded from PLSQL/calc_secs_between.sp

This sample was taken from the examples that come with the utPLSQL tool. In real life we would probably not bother testing this code because it is so simple (but then again, maybe it is not ...?) but it will work just fine to illustrate how we could go about testing it.

Refactoring Notes

This isn't so much about refactoring as it is about adding a missing test. Let's find a way to write one. We see what is involved in doing using the two main variants: In-Database Stored Procedure Test and Remote Stored Procedure Test.

Example: In-Database Stored Procedure Test

Here is an example that uses utPLSQL, the xUnit family member for PLSQL, to automate tests that run inside the database:

CREATE OR REPLACE PACKAGE BODY ut_calc_secs_between
IS
   PROCEDURE ut_setup
   IS
   BEGIN
      NULL;
   END;
  
   PROCEDURE ut_teardown
   IS
   BEGIN
      NULL;
   END;

   -- For each program to test...PROCEDURE ut_CALC_SECS_BETWEEN
   IS
      secs PLS_INTEGER;
   BEGIN
      CALC_SECS_BETWEEN ( DATE1 => SYSDATE
            ,
            DATE2 => SYSDATE
            ,
            SECS => secs
       );

      utAssert.eq (
         'Same dates',
         secs,
         0
         );
   END ut_CALC_SECS_BETWEEN;

END ut_calc_secs_between;
/
Example InDbStoredProcTest embedded from PLSQL/ut_calc_secs_between.pkb

Note how this test uses many of the familiar xUnit patterns. This would be one of several tests we would normally write for this stored procedure, one for each possible scenario. (This sample was taken from the examples that come with the utPLSQL tool. Not being a PLSQL programmer I did not want to mess with the formatting in case it mattered!)

Example: Remote Stored Procedure Test

To make it possible to test this stored procedure for our normal programming and test execution environment, we must first find or create a Remote Proxy for it in our unit testing environment of choice. Then we can write our unit tests in the usual manner.

Here is a test that uses JUnit to automate tests that run outside the database:

public class StoredProcedureTest extends TestCase {
   public void testCalcSecsBetween_SameTime() {
      // Setup:
      TimeCalculatorProxy SUT = new TimeCalculatorProxy();
      Calendar cal = new GregorianCalendar();
      long now = cal.getTimeInMillis();
      // Exercise:
      long timeDifference = SUT.calc_secs_between(now,now);
      // Verify:
      assertEquals( 0, timeDifference );
   }
}
Example RemoteStoredProcTest embedded from java/com/xunitpatterns/plsql/StoredProcedureTest.java

We have reduced the complexity of the test to a simple test of a function by hiding the JdbcOdbcCallableStatement behind a Service Facade. Looking at this sample, it is pretty hard to tell that we are not testing a Java method. We would probably have additional Expected Exception Tests (see Test Method on page X) to verify failed connections, etc.

Page generated at Wed Feb 09 16:39:33 +1100 2011